<?php
/* -------------------------------------------------------------------------------------
* 	ID:						$Id: import.php 11 2013-09-21 11:43:11Z phone.mueller@googlemail.com $
* 	Letzter Stand:			$Revision: 11 $
* 	zuletzt geaendert von:	$Author: peter $
* 	Datum:					$Date: 2013-09-21 11:43:11 +0000 (Sat, 21 Sep 2013) $
*
* 	SEO:mercari by Siekiera Media
* 	http://www.seo-mercari.de
*
* 	Copyright (c) since 2011 SEO:mercari
* --------------------------------------------------------------------------------------
* 	based on:
* 	(c) 2000-2001 The Exchange Project  (earlier name of osCommerce)
* 	(c) 2002-2003 osCommerce - www.oscommerce.com
* 	(c) 2003     nextcommerce - www.nextcommerce.org
* 	(c) 2005     xt:Commerce - www.xt-commerce.com
*
* 	Released under the GNU General Public License
* ----------------------------------------------------------------------------------- */

defined('_VALID_XTC') or die('Direct Access to this location is not allowed.');

class Import {
	var $seperator;
	var $TextSign;
	var $db;

	function __construct($filename) {
		global $db;

		$this->seperator = CSV_SEPERATOR;
		$this->TextSign = CSV_TEXTSIGN;
		$this->db = $db;

		if (CSV_SEPERATOR == '')
			$this->seperator = "\t";

		if (CSV_SEPERATOR == '\t')
			$this->seperator = "\t";

		$this->filename = $filename;
		$this->ImportDir = DIR_FS_CATALOG.'import/';
		$this->catDepth = 6;
		$this->languages = $this->get_lang();
		$this->counter = array ('prod_new' => 0, 'cat_new' => 0, 'prod_upd' => 0, 'cat_upd' => 0);
		$this->mfn = $this->get_mfn();
		$this->errorlog = array ();
		$this->time_start = time();
		$this->debug = false;
		$this->CatTree = array ('ID' => 0);

		$this->CatCache = true;
		$this->FileSheme = array ();
		$this->Groups = get_customers_statuses();

	}

	/**
	*   generating file layout
	*   @param array $mapping standard fields
	*   @return array
	*/
	function generate_map() {

		// lets define a standard fieldmapping array, with importable fields
		$file_layout = array (	'p_model' => '', // products_model
								'p_stock' => '', // products_quantity
								'p_tpl' => '', // products_template
								'p_sorting' => '', // products_sorting
								'p_brand' => '', // brand
								'p_fsk18' => '', // FSK18
								'p_priceNoTax' => '', // Nettoprice
								'p_tax' => '', // taxrate in percent
								'p_status' => '', // products status
								'p_weight' => '', // products weight
								'p_ean' => '', // products ean
								'p_disc' => '', // products discount
								'p_opttpl' => '', // options template
								'p_image' => '', // product image
								'p_vpe' => '', // products VPE
								'p_vpe_status' => '', // products VPE Status
								'p_vpe_value' => '', // products VPE value
								'p_shipping' => '' // product shipping_time
								);

		// Group Prices
		for ($i = 0; $i < count($this->Groups) - 1; $i++)
			$file_layout = array_merge($file_layout, array ('p_priceNoTax.'.$this->Groups[$i +1]['id'] => ''));

		// Group Permissions
		for ($i = 0; $i < count($this->Groups) - 1; $i++)
			$file_layout = array_merge($file_layout, array ('p_groupAcc.'.$this->Groups[$i +1]['id'] => ''));

		// product images
		for ($i = 1; $i < MO_PICS + 1; $i++)
			$file_layout = array_merge($file_layout, array ('p_image.'.$i => ''));

		// add lang fields
		for ($i = 0; $i < sizeof($this->languages); $i++) {
			$file_layout = array_merge($file_layout, array ('p_name.'.$this->languages[$i]['code'] => '',
															'p_desc.'.$this->languages[$i]['code'] => '',
															'p_shortdesc.'.$this->languages[$i]['code'] => '',
															'p_meta_title.'.$this->languages[$i]['code'] => '',
															'p_meta_desc.'.$this->languages[$i]['code'] => '',
															'p_meta_key.'.$this->languages[$i]['code'] => '',
															'p_keywords.'.$this->languages[$i]['code'] => '',
															'p_url.'.$this->languages[$i]['code'] => ''));
		}
		// add categorie fields
		for ($i = 0; $i < $this->catDepth; $i ++)
			$file_layout = array_merge($file_layout, array ('p_cat.'.$i => ''));

		return $file_layout;
	}

	/**
	*   generating mapping layout for importfile
	*   @param array $mapping standard fields
	*   @return array
	*/
	function map_file($mapping) {
		if (!file_exists($this->ImportDir.$this->filename)) {
			// error
			return 'error';

		} else {
			// file is ok, creating mapping
			$inhalt = array ();
			$inhalt = file($this->ImportDir.$this->filename);
			// get first line into array
			$content = explode($this->seperator, $inhalt[0]);

			foreach ($mapping as $key => $value) {
				// try to find our field in fieldlayout
				foreach ($content as $key_c => $value_c)
					if ($key == trim($this->RemoveTextNotes($content[$key_c]))) {
						$mapping[$key] = trim($this->RemoveTextNotes($key_c));
						$this->FileSheme[$key] = 'Y';
					}
			}
			return $mapping;
		}
	}

	/**
	*   Get installed languages
	*   @return array
	*/
	function get_lang() {

		$languages = $this->db->db_query("SELECT
												languages_id,
												name,
												code,
												image,
												directory
											FROM
												".TABLE_LANGUAGES."
											ORDER BY
												sort_order");

		while (!$languages->EOF) {
			$languages_array[] = array ('id' => $languages->fields['languages_id'],
										'name' => $languages->fields['name'],
										'code' => $languages->fields['code']);
			$languages->MoveNext();
		}
		return $languages_array;
	}

	function import($mapping) {
		// open file
		$inhalt = file($this->ImportDir.$this->filename);
		$lines = count($inhalt);

		for ($i = 1; $i < $lines; $i ++) {
			$line_content = '';

			$line_fetch = $this->get_line_content($i, $inhalt, $lines);
			$line_content = explode($this->seperator, $line_fetch['data']);
			$i += $line_fetch['skip'];

			$line_data = $this->generate_map();

			foreach ($mapping as $key => $value)
				$line_data[$key] = $this->RemoveTextNotes($line_content[$value]);

			if ($this->debug) {
				echo '<pre>';
				print_r($line_data);
				echo '</pre>';

			}

			if ($line_data['p_model'] != '') {
				if ($line_data['p_cat.0'] != '' || $this->FileSheme['p_cat.0'] != 'Y') {
					if ($this->FileSheme['p_cat.0'] != 'Y') {
						if ($this->checkModel($line_data['p_model']))
							$this->insertProduct($line_data, 'update');
						else
							$this->insertProduct($line_data,'insert');

					} else {
						if ($this->checkModel($line_data['p_model']))
							$this->insertProduct($line_data, 'update',true);
						else
							$this->insertProduct($line_data,'insert',true);
					}
				} else
					$this->errorLog[] = '<b>ERROR:</b> keine kategorie, Zeile: '.$i.' Datensatz: '.$line_fetch['data'];

			} else
				$this->errorLog[] = '<b>ERROR:</b> keine Modellnummer, Zeile: '.$i.' Datensatz: '.$line_fetch['data'];

		}
		return array ($this->counter, $this->errorLog, $this->calcElapsedTime($this->time_start));
	}

	/**
	*   Check if a product exists in database, query for model number
	*   @param string $model products modelnumber
	*   @return boolean
	*/
	function checkModel($model) {
		$model = $this->db->db_query("SELECT
											products_id
										FROM
											".TABLE_PRODUCTS."
										WHERE
											products_model='".addslashes($model)."'");
		if($model->_numOfRows < 1)
			return false;

		return true;
	}

	/**
	*   Check if a image exists
	*   @param string $model products modelnumber
	*   @return boolean
	*/
	function checkImage($imgID,$pID) {
		$img = $this->db->db_query("SELECT
											image_id
										FROM
											".TABLE_PRODUCTS_IMAGES."
										WHERE
											products_id='".$pID."'
										AND
											image_nr='".$imgID."'");
		if($img->_numOfRows < 1)
			return false;

		return true;
	}

	/**
	*   removing textnotes from a dataset
	*   @param String $data data
	*   @return String cleaned data
	*/
	function RemoveTextNotes($data) {
		if (substr($data, -1) == $this->TextSign)
			$data = substr($data, 1, strlen($data) - 2);

		return $data;

	}

	/**
	*   Get/create brand ID for a given Name
	*   @param String $brand brand name
	*   @return int brand ID
	*/
	function getMAN($brand) {
		if($brand == '')
			return;

		if(isset ($this->mfn[$brand]['id']))
			return $this->mfn[$brand]['id'];

		$man = $this->db->db_query("SELECT
										brand_id
									FROM
										".TABLE_BRAND."
									WHERE
										brand_name = '".$brand."'");
		if($man->_numOfRows > 0) {
			$brand_array = array ('brand_name' => $brand);
			$this->db->db_perform(TABLE_BRAND, $brand_array);
			$this->mfn[$brand]['id'] = $this->db->db_insert_id();

		} else
			$this->mfn[$brand]['id'] = $man->fields['brand_id'];

		return $this->mfn[$brand]['id'];
	}
	/**
	*   Insert a new product into Database
	*   @param array $dataArray Linedata
	*   @param string $mode insert or update flag
	*/
	function insertProduct(& $dataArray, $mode = 'insert',$touchCat = false) {

		$products_array = array ('products_model' => $dataArray['p_model']);
		if ($this->FileSheme['p_stock'] == 'Y')
			$products_array = array_merge($products_array, array ('products_quantity' => $dataArray['p_stock']));
		if ($this->FileSheme['p_priceNoTax'] == 'Y')
			$tax_rate = get_tax_rate($dataArray['p_tax']);
			if (BRUTTO_EXPORT)
				$products_array = array_merge($products_array, array ('products_price' => str_replace(DEZIMAL_EXPORT,".",$dataArray['p_priceNoTax'])/ (1+$tax_rate/100)));
			else
				$products_array = array_merge($products_array, array ('products_price' => str_replace(DEZIMAL_EXPORT,".",$dataArray['p_priceNoTax'])));

		if ($this->FileSheme['p_weight'] == 'Y')
			$products_array = array_merge($products_array, array ('products_weight' => $dataArray['p_weight']));
		if ($this->FileSheme['p_status'] == 'Y')
			$products_array = array_merge($products_array, array ('products_status' => $dataArray['p_status']));
		if ($this->FileSheme['p_image'] == 'Y')
			$products_array = array_merge($products_array, array ('products_image' => $dataArray['p_image']));
		if ($this->FileSheme['p_disc'] == 'Y')
			$products_array = array_merge($products_array, array ('products_discount_allowed' => $dataArray['p_disc']));
		if ($this->FileSheme['p_ean'] == 'Y')
			$products_array = array_merge($products_array, array ('products_ean' => $dataArray['p_ean']));
		if ($this->FileSheme['p_tax'] == 'Y')
			$products_array = array_merge($products_array, array ('products_tax_class_id' => $dataArray['p_tax']));
		if ($this->FileSheme['p_opttpl'] == 'Y')
			$products_array = array_merge($products_array, array ('options_template' => $dataArray['p_opttpl']));
		if ($this->FileSheme['p_brand'] == 'Y')
			$products_array = array_merge($products_array, array ('brand_id' => $this->getMAN(trim($dataArray['p_brand']))));
		if ($this->FileSheme['p_fsk18'] == 'Y')
			$products_array = array_merge($products_array, array ('products_fsk18' => $dataArray['p_fsk18']));
		if ($this->FileSheme['p_tpl'] == 'Y')
			$products_array = array_merge($products_array, array ('product_template' => $dataArray['p_tpl']));
		if ($this->FileSheme['p_vpe'] == 'Y')
			$products_array = array_merge($products_array, array ('products_vpe' => $dataArray['p_vpe']));
		if ($this->FileSheme['p_vpe_status'] == 'Y')
			$products_array = array_merge($products_array, array ('products_vpe_status' => $dataArray['p_vpe_status']));
		if ($this->FileSheme['p_vpe_value'] == 'Y')
			$products_array = array_merge($products_array, array ('products_vpe_value' => $dataArray['p_vpe_value']));
		if ($this->FileSheme['p_shipping'] == 'Y')
			$products_array = array_merge($products_array, array ('products_shippingtime' => $dataArray['p_shipping']));
		if ($this->FileSheme['p_sorting'] == 'Y')
			$products_array = array_merge($products_array, array ('products_sort' => $dataArray['p_sorting']));

		if ($mode == 'insert') {
			$this->counter['prod_new']++;
			$this->db->db_perform(TABLE_PRODUCTS, $products_array);
			$products_id = $this->db->db_insert_id();

		} else {
			$this->counter['prod_upd']++;
			$this->db->db_perform(TABLE_PRODUCTS, $products_array, 'update', "products_model = '".addslashes($dataArray['p_model'])."'");
			$prod = $this->db->db_query("SELECT
												products_id
											FROM
												".TABLE_PRODUCTS."
											WHERE
												products_model='".addslashes($dataArray['p_model'])."'");
			$products_id = $prod->fields['products_id'];

		}

		// Insert Group Prices.
		for ($i = 0; $i < count($this->Groups) - 1; $i ++) {
			// seperate string ::
			if (isset ($dataArray['p_priceNoTax.'.$this->Groups[$i +1]['id']])) {
				$this->db->db_query("DELETE FROM
										".TABLE_PERSONAL_OFFERS_BY.$this->Groups[$i +1]['id']."
									WHERE
										products_id='".$prod_data['products_id']."'");

				$prices = $dataArray['p_priceNoTax.'.$this->Groups[$i +1]['id']];
				$prices = explode('::', $prices);
				for ($ii = 0; $ii < count($prices); $ii ++) {
					$values = explode(':', $prices[$ii]);
					$group_array = array ('products_id' => $prod_data['products_id'], 'quantity' => $values[0], 'personal_offer' => $values[1]);

					$this->db->db_perform(TABLE_PERSONAL_OFFERS_BY.$this->Groups[$i +1]['id'], $group_array);
				}
			}
		}

		// Insert Group Permissions.
		for ($i = 0; $i < count($this->Groups) - 1; $i ++) {
			// seperate string ::
			if (isset ($dataArray['p_groupAcc.'.$this->Groups[$i +1]['id']])) {
				$insert_array = array ('group_permission_'.$this->Groups[$i +1]['id'] => $dataArray['p_groupAcc.'.$this->Groups[$i +1]['id']]);
				$this->db->db_perform(TABLE_PRODUCTS, $insert_array, 'update', 'products_id = \''.$products_id.'\'');
			}
		}

		// insert images
		for ($i = 1; $i < MO_PICS + 1; $i ++) {
			if (isset($dataArray['p_image.'.$i]) && $dataArray['p_image.'.$i]!="") {
				// check if entry exists
				if ($this->checkImage($i,$products_id)) {
					$insert_array = array ('image_name' => $dataArray['p_image.'.$i]);
					$this->db->db_perform(TABLE_PRODUCTS_IMAGES, $insert_array, 'update', 'products_id = \''.$products_id.'\' and image_nr=\''.$i.'\'');

				} else {
					$insert_array = array ('image_name' => $dataArray['p_image.'.$i],'image_nr'=>$i,'products_id'=>$products_id);
					$this->db->db_perform(TABLE_PRODUCTS_IMAGES, $insert_array);
				}
			}
		}

		if ($touchCat) $this->insertCategory($dataArray, $mode, $products_id);
		for ($i_insert = 0; $i_insert < sizeof($this->languages); $i_insert ++) {
			$prod_desc_array = array ('products_id' => $products_id, 'language_id' => $this->languages[$i_insert]['id']);

			if ($this->FileSheme['p_name.'.$this->languages[$i_insert]['code']] == 'Y')
				$prod_desc_array = array_merge($prod_desc_array, array ('products_name' => addslashes($dataArray['p_name.'.$this->languages[$i_insert]['code']])));
			if ($this->FileSheme['p_desc.'.$this->languages[$i_insert]['code']] == 'Y')
				$prod_desc_array = array_merge($prod_desc_array, array ('products_description' => addslashes($dataArray['p_desc.'.$this->languages[$i_insert]['code']])));
			if ($this->FileSheme['p_shortdesc.'.$this->languages[$i_insert]['code']] == 'Y')
				$prod_desc_array = array_merge($prod_desc_array, array ('products_short_description' => addslashes($dataArray['p_shortdesc.'.$this->languages[$i_insert]['code']])));
			if ($this->FileSheme['p_meta_title.'.$this->languages[$i_insert]['code']] == 'Y')
				$prod_desc_array = array_merge($prod_desc_array, array ('products_meta_title' => $dataArray['p_meta_title.'.$this->languages[$i_insert]['code']]));
			if ($this->FileSheme['p_meta_desc.'.$this->languages[$i_insert]['code']] == 'Y')
				$prod_desc_array = array_merge($prod_desc_array, array ('products_meta_description' => $dataArray['p_meta_desc.'.$this->languages[$i_insert]['code']]));
			if ($this->FileSheme['p_meta_key.'.$this->languages[$i_insert]['code']] == 'Y')
				$prod_desc_array = array_merge($prod_desc_array, array ('products_meta_keywords' => $dataArray['p_meta_key.'.$this->languages[$i_insert]['code']]));
			if ($this->FileSheme['p_keywords.'.$this->languages[$i_insert]['code']] == 'Y')
				$prod_desc_array = array_merge($prod_desc_array, array ('products_keywords' => $dataArray['p_keywords.'.$this->languages[$i_insert]['code']]));
			if ($this->FileSheme['p_url.'.$this->languages[$i_insert]['code']] == 'Y')
				$prod_desc_array = array_merge($prod_desc_array, array ('products_url' => $dataArray['p_url.'.$this->languages[$i_insert]['code']]));

			if ($mode == 'insert') {
				$this->db->db_perform(TABLE_PRODUCTS_DESCRIPTION, $prod_desc_array);
			} else {
				$this->db->db_perform(TABLE_PRODUCTS_DESCRIPTION, $prod_desc_array, 'update', 'products_id = \''.$products_id.'\' and language_id=\''.$this->languages[$i_insert]['id'].'\'');
			}
		}
	}

	/**
	*   Match and insert Categories
	*   @param array $dataArray data array
	*   @param string $mode insert mode
	*   @param int $pID  products ID
	*/
	function insertCategory(& $dataArray, $mode = 'insert', $pID) {
		if ($this->debug) {
			echo '<pre>';
			print_r($this->CatTree);
			echo '</pre>';
		}
		$cat = array ();
		$catTree = '';
		for ($i = 0; $i < $this->catDepth; $i ++)
			if (trim($dataArray['p_cat.'.$i]) != '') {
				$cat[$i] = trim($dataArray['p_cat.'.$i]);
				$catTree .= '[\''.addslashes($cat[$i]).'\']';
			}
		$code = '$ID=$this->CatTree'.$catTree.'[\'ID\'];';
		if ($this->debug)
			echo $code;
		eval ($code);

		if (is_int($ID) || $ID == '0')
			$this->insertPtoCconnection($pID, $ID);

		else {

			$catTree = '';
			$parTree = '';
			$curr_ID = 0;
			for ($i = 0; $i < count($cat); $i ++) {

				$catTree .= '[\''.addslashes($cat[$i]).'\']';

				$code = '$ID=$this->CatTree'.$catTree.'[\'ID\'];';
				eval ($code);
				if (is_int($ID) || $ID == '0') {
					$curr_ID = $ID;
				} else {

					$code = '$parent=$this->CatTree'.$parTree.'[\'ID\'];';
					eval ($code);

					$cat_data = $this->db->db_query("SELECT
														c.categories_id
													FROM
														".TABLE_CATEGORIES." c,
														".TABLE_CATEGORIES_DESCRIPTION." cd
			                                        WHERE
			                                            cd.categories_name = '".addslashes($cat[$i])."'
			                                        AND
			                                        	cd.language_id = '".$this->languages[0]['id']."'
			                                        AND
			                                        	cd.categories_id = c.categories_id
			                                        AND
			                                        	parent_id = '".$parent."'");

					if($cat_data->_numOfRows > 0) {
						$categorie_data = array ('parent_id' => $parent, 'categories_status' => 1, 'date_added' => 'now()', 'last_modified' => 'now()');

						$this->db->db_perform(TABLE_CATEGORIES, $categorie_data);
						$cat_id = $this->db->db_insert_id();
						$this->counter['cat_new']++;
						$code = '$this->CatTree'.$parTree.'[\''.addslashes($cat[$i]).'\'][\'ID\']='.$cat_id.';';
						eval ($code);
						$parent = $cat_id;
							for ($i_insert = 0; $i_insert < sizeof($this->languages); $i_insert ++) {
								$categorie_data = array ('language_id' => $this->languages[$i_insert]['id'], 'categories_id' => $cat_id, 'categories_name' => $cat[$i]);
								$this->db->db_perform(TABLE_CATEGORIES_DESCRIPTION, $categorie_data);
							}

					} else {
						$this->counter['cat_touched']++;
						$code = '$this->CatTree'.$parTree.'[\''.addslashes($cat[$i]).'\'][\'ID\']='.$cat_data->fields['categories_id'].';';
						eval($code);
					}
				}
				$parTree = $catTree;
			}
			$this->insertPtoCconnection($pID, $cat_id);
		}
	}

	/**
	*   Insert products to categories connection
	*   @param int $pID products ID
	*   @param int $cID categories ID
	*/
	function insertPtoCconnection($pID, $cID) {
		$prod2cat = $this->db->db_query("SELECT
													*
												FROM
													".TABLE_PRODUCTS_TO_CATEGORIES."
												WHERE
													categories_id='".$cID."'
												AND
													products_id='".$pID."'");

		if($prod2cat->_numOfRows < 1) {
			$insert_data = array ('products_id' => $pID, 'categories_id' => $cID);
			$this->db->db_perform(TABLE_PRODUCTS_TO_CATEGORIES, $insert_data);
		}
	}

	/**
	*   Parse Inputfile until next line
	*   @param int $line taxrate in percent
	*   @param string $file_content taxrate in percent
	*   @param int $max_lines taxrate in percent
	*   @return array
	*/
	function get_line_content($line, $file_content, $max_lines) {
		// get first line
		$line_data = array ();
		$line_data['data'] = $file_content[$line];
		$lc = 1;
		// check if next line got ; in first 50 chars
		while(!strstr(substr($file_content[$line + $lc], 0, 8), 'MERCARI') && $line + $lc <= $max_lines) {
			$line_data['data'] .= $file_content[$line + $lc];
			$lc ++;
		}
		$line_data['skip'] = $lc -1;
		return $line_data;
	}

	/**
	*   Calculate Elapsed time from 2 given Timestamps
	*   @param int $time old timestamp
	*   @return String elapsed time
	*/
	function calcElapsedTime($time) {

		// calculate elapsed time (in seconds!)
		$diff = time() - $time;
		$daysDiff = 0;
		$hrsDiff = 0;
		$minsDiff = 0;
		$secsDiff = 0;

		$sec_in_a_day = 60 * 60 * 24;
		while ($diff >= $sec_in_a_day) {
			$daysDiff ++;
			$diff -= $sec_in_a_day;
		}
		$sec_in_an_hour = 60 * 60;
		while ($diff >= $sec_in_an_hour) {
			$hrsDiff ++;
			$diff -= $sec_in_an_hour;
		}
		$sec_in_a_min = 60;
		while ($diff >= $sec_in_a_min) {
			$minsDiff ++;
			$diff -= $sec_in_a_min;
		}
		$secsDiff = $diff;

		return ('(elapsed time '.$hrsDiff.'h '.$minsDiff.'m '.$secsDiff.'s)');
	}

	/**
	*   Get brand
	*   @return array
	*/
	function get_mfn() {
		$mfn = $this->db->db_query("SELECT
										brand_id,
										brand_name
									FROM
										".TABLE_BRAND);

		while(!$mfn->EOF) {
			$mfn_array[$mfn->fields['brand_name']] = array ('id' => $mfn->fields['brand_id']);
			$mfn->MoveNext();
		}
		return $mfn_array;
	}

}

class Export {
	var $db;

	function __construct($filename) {
		global $db;

		$this->db = $db;
		$this->catDepth = 6;
		$this->languages = $this->get_lang();
		$this->filename = $filename;
		$this->CAT = array ();
		$this->PARENT = array ();
		$this->counter = array ('prod_exp' => 0);
		$this->time_start = time();
		$this->man = $this->getbrand();
		$this->TextSign = CSV_TEXTSIGN;
		$this->seperator = CSV_SEPERATOR;

		if(CSV_SEPERATOR == '')
			$this->seperator = "\t";

		if(CSV_SEPERATOR == '\t')
			$this->seperator = "\t";

		$this->Groups = get_customers_statuses();
	}

	/**
	*   Get installed languages
	*   @return array
	*/
	function get_lang() {

		$languages = $this->db->db_query("SELECT
												languages_id,
												name,
												code,
												image,
												directory
											FROM
												".TABLE_LANGUAGES."
											ORDER BY
												sort_order");

		while (!$languages->EOF) {
			$languages_array[] = array ('id' => $languages->fields['languages_id'],
										'name' => $languages->fields['name'],
										'code' => $languages->fields['code']);
			$languages->MoveNext();
		}
		return $languages_array;
	}

	function exportProdFile() {
		require_once (DIR_FS_INC.'inc.has_category_subcategories.php');
		require_once (DIR_FS_INC.'inc.get_subcategories.php');
		require_once (DIR_FS_INC.'inc.get_tax_rate.php');

		$fp = fopen(DIR_FS_DOCUMENT_ROOT.'export/'.$this->filename, "w+");
		$heading = $this->TextSign.'MERCARI'.$this->TextSign.$this->seperator;
		$heading .= $this->TextSign.'p_model'.$this->TextSign.$this->seperator;
		$heading .= $this->TextSign.'p_stock'.$this->TextSign.$this->seperator;
		$heading .= $this->TextSign.'p_sorting'.$this->TextSign.$this->seperator;
		$heading .= $this->TextSign.'p_shipping'.$this->TextSign.$this->seperator;
		$heading .= $this->TextSign.'p_tpl'.$this->TextSign.$this->seperator;
		$heading .= $this->TextSign.'p_brand'.$this->TextSign.$this->seperator;
		$heading .= $this->TextSign.'p_fsk18'.$this->TextSign.$this->seperator;
		$heading .= $this->TextSign.'p_priceNoTax'.$this->TextSign.$this->seperator;
		for ($i = 0; $i < count($this->Groups) - 1; $i ++)
			$heading .= $this->TextSign.'p_priceNoTax.'.$this->Groups[$i +1]['id'].$this->TextSign.$this->seperator;

		if (GROUP_CHECK == 'true') {
			for ($i = 0; $i < count($this->Groups) - 1; $i ++)
				$heading .= $this->TextSign.'p_groupAcc.'.$this->Groups[$i +1]['id'].$this->TextSign.$this->seperator;

		}
		$heading .= $this->TextSign.'p_tax'.$this->TextSign.$this->seperator;
		$heading .= $this->TextSign.'p_status'.$this->TextSign.$this->seperator;
		$heading .= $this->TextSign.'p_weight'.$this->TextSign.$this->seperator;
		$heading .= $this->TextSign.'p_ean'.$this->TextSign.$this->seperator;
		$heading .= $this->TextSign.'p_disc'.$this->TextSign.$this->seperator;
		$heading .= $this->TextSign.'p_opttpl'.$this->TextSign.$this->seperator;
		$heading .= $this->TextSign.'p_vpe'.$this->TextSign.$this->seperator;
		$heading .= $this->TextSign.'p_vpe_status'.$this->TextSign.$this->seperator;
		$heading .= $this->TextSign.'p_vpe_value'.$this->TextSign.$this->seperator;
		// product images

		for ($i = 1; $i < MO_PICS + 1; $i ++) {
			$heading .= $this->TextSign.'p_image.'.$i.$this->TextSign.$this->seperator;
		}

		$heading .= $this->TextSign.'p_image'.$this->TextSign;

		// add lang fields
		for ($i = 0; $i < sizeof($this->languages); $i ++) {
			$heading .= $this->seperator.$this->TextSign;
			$heading .= 'p_name.'.$this->languages[$i]['code'].$this->TextSign.$this->seperator;
			$heading .= $this->TextSign.'p_desc.'.$this->languages[$i]['code'].$this->TextSign.$this->seperator;
			$heading .= $this->TextSign.'p_shortdesc.'.$this->languages[$i]['code'].$this->TextSign.$this->seperator;
			$heading .= $this->TextSign.'p_meta_title.'.$this->languages[$i]['code'].$this->TextSign.$this->seperator;
			$heading .= $this->TextSign.'p_meta_desc.'.$this->languages[$i]['code'].$this->TextSign.$this->seperator;
			$heading .= $this->TextSign.'p_meta_key.'.$this->languages[$i]['code'].$this->TextSign.$this->seperator;
			$heading .= $this->TextSign.'p_keywords.'.$this->languages[$i]['code'].$this->TextSign.$this->seperator;
			$heading .= $this->TextSign.'p_url.'.$this->languages[$i]['code'].$this->TextSign;

		}
		// add categorie fields
		for ($i = 0; $i < $this->catDepth; $i ++)
			$heading .= $this->seperator.$this->TextSign.'p_cat.'.$i.$this->TextSign;

		$heading .= "\n";

		fputs($fp, $heading);
		/*
		echo '<pre>';
		print_r($_POST);
		echo '</pre>'; exit;
		*/
		$selected_cat = $_POST['select_cat'];
		$selected_man = $_POST['select_man'];
		$export_data = array();

		if($selected_cat == 0 && $selected_man == 0) {
			$export = $this->db->db_query("SELECT * FROM ".TABLE_PRODUCTS);
			for ($i = 0; $i < $export->_numOfRows; $i++)
				$export_data[] = $export->fields;
		}
		if ($selected_cat==0 && $selected_man > 0) {
			$export = $this->db->db_query("SELECT * FROM ".TABLE_PRODUCTS." WHERE brand_id = '".$selected_man."'");
			for ($i = 0; $i < $export->_numOfRows; $i++)
				$export_data[] = $export->fields;
		}

		if($selected_cat > 0) {
			if(has_category_subcategories($selected_cat)) {
				get_subcategories($sub_categories, $selected_cat);
				for ($i = 0; $i < count($sub_categories); $i++) {
					if ($selected_man == 0) {
						$cat_export = $this->db->db_query("SELECT
																p.*
															FROM
																".TABLE_PRODUCTS." p
																INNER JOIN
																	".TABLE_PRODUCTS_TO_CATEGORIES." p2c ON p.products_id = p2c.products_id
															WHERE
																p2c.categories_id = '".$sub_categories[$i]."'");
					} else {
						$cat_export = $this->db->db_query("SELECT
																p.*
															FROM
																".TABLE_PRODUCTS." p
																INNER JOIN
																	".TABLE_PRODUCTS_TO_CATEGORIES." p2c ON p.products_id = p2c.products_id
															WHERE
																p2c.categories_id = '".$sub_categories[$i]."'
															AND
																p.brand_id = '".$selected_man."'");
					}
					//now add products to eport_data
					while(!$cat_export->EOF) {
						$export_data[] = $produkt->fields;
						$cat_export->MoveNext();
					}
				}
			} else {
				//keine unterkategorien => nur Produkte dieser Kategorie exportieren!
				if ($selected_man == 0) {
					$export = $this->db->db_query("SELECT
														p.*
													FROM
														".TABLE_PRODUCTS." p
														INNER JOIN
															".TABLE_PRODUCTS_TO_CATEGORIES." p2c ON p.products_id = p2c.products_id
													WHERE
														p2c.categories_id = '".$selected_cat."'");
				} else {
					$export = $this->db->db_query("SELECT
														p.* FROM ".TABLE_PRODUCTS." p
														INNER JOIN ".TABLE_PRODUCTS_TO_CATEGORIES." p2c ON p.products_id = p2c.products_id
													WHERE
														p2c.categories_id = '".$selected_cat."'
													AND
														p.brand_id = '".$selected_man."'");
				}
				for($i = 0; $i < $export->_numOfRows; $i++)
					$export_data[] = $export->fields;
			}
		}

		for ($j = 0; $j < count($export_data); $j++) {

			$this->counter['prod_exp']++;
			$line = $this->TextSign.'MERCARI'.$this->TextSign.$this->seperator;
			$line .= $this->TextSign.$export_data[$j]['products_model'].$this->TextSign.$this->seperator;
			$line .= $this->TextSign.$export_data[$j]['products_quantity'].$this->TextSign.$this->seperator;
			$line .= $this->TextSign.$export_data[$j]['products_sort'].$this->TextSign.$this->seperator;
			$line .= $this->TextSign.$export_data[$j]['products_shippingtime'].$this->TextSign.$this->seperator;
			$line .= $this->TextSign.$export_data[$j]['product_template'].$this->TextSign.$this->seperator;
			$line .= $this->TextSign.$this->man[$export_data[$j]['brand_id']].$this->TextSign.$this->seperator;
			$line .= $this->TextSign.$export_data[$j]['products_fsk18'].$this->TextSign.$this->seperator;

			$tax_rate = get_tax_rate($export_data[$j]['products_tax_class_id']);
			if (BRUTTO_EXPORT)
				$line .= $this->TextSign.str_replace(".", DEZIMAL_EXPORT, $export_data[$j]['products_price'] * (1+$tax_rate/100)).$this->TextSign.$this->seperator;
			else
				$line .= $this->TextSign.str_replace(".", DEZIMAL_EXPORT, $export_data[$j]['products_price']).$this->TextSign.$this->seperator;

			// group prices  Qantity:Price::Quantity:Price
			for ($i = 0; $i < count($this->Groups) - 1; $i ++) {
				$price = $this->db->db_query("SELECT * FROM ".TABLE_PERSONAL_OFFERS_BY.$this->Groups[$i +1]['id']." WHERE products_id = '".$export_data[$j]['products_id']."'ORDER BY quantity");
				$groupPrice = '';
				while(!$price->EOF) {
					if ($price->fields['personal_offer'] > 0) {
						$groupPrice .= $price_data->fields['quantity'].':'.$price_data->fields['personal_offer'].'::';
					}
					$price->MoveNext();
				}
				$groupPrice .= ':';
				$groupPrice = str_replace(':::', '', $groupPrice);
				if ($groupPrice == ':')
					$groupPrice = "";
				$line .= $this->TextSign.$groupPrice.$this->TextSign.$this->seperator;

			}

			// group permissions
			if (GROUP_CHECK == 'true') {
				for ($i = 0; $i < count($this->Groups) - 1; $i ++) {
					$line .= $this->TextSign.$lang_data['group_permission_'.$this->Groups[$i +1]['id']].$this->TextSign.$this->seperator;
				}
			}

			$line .= $this->TextSign.$export_data[$j]['products_tax_class_id'].$this->TextSign.$this->seperator;
			$line .= $this->TextSign.$export_data[$j]['products_status'].$this->TextSign.$this->seperator;
			$line .= $this->TextSign.$export_data[$j]['products_weight'].$this->TextSign.$this->seperator;
			$line .= $this->TextSign.$export_data[$j]['products_ean'].$this->TextSign.$this->seperator;
			$line .= $this->TextSign.$export_data[$j]['products_discount_allowed'].$this->TextSign.$this->seperator;
			$line .= $this->TextSign.$export_data[$j]['options_template'].$this->TextSign.$this->seperator;
			$line .= $this->TextSign.$export_data[$j]['products_vpe'].$this->TextSign.$this->seperator;
			$line .= $this->TextSign.$export_data[$j]['products_vpe_status'].$this->TextSign.$this->seperator;
			$line .= $this->TextSign.$export_data[$j]['products_vpe_value'].$this->TextSign.$this->seperator;

			if (MO_PICS > 0) {
				$mo_img = $this->db->db_query("SELECT * FROM ".TABLE_PRODUCTS_IMAGES." WHERE products_id='".$export_data[$j]['products_id']."'");

				$img = array ();
				while (!$mo_img->EOF) {
					$img[$mo_img->fields['image_nr']] = $mo_img->fields['image_name'];
					$mo_img->MoveNext();
				}
			}

			// product images
			for ($i = 1; $i < MO_PICS + 1; $i ++) {
				if(isset($img[$i]))
					$line .= $this->TextSign.$img[$i].$this->TextSign.$this->seperator;
				else
					$line .= $this->TextSign."".$this->TextSign.$this->seperator;
			}

			$line .= $this->TextSign.$export_data[$j]['products_image'].$this->TextSign.$this->seperator;

			for ($i = 0; $i < sizeof($this->languages); $i ++) {
				$lang_data = $this->db->db_query("SELECT * FROM ".TABLE_PRODUCTS_DESCRIPTION." WHERE language_id='".$this->languages[$i]['id']."' and products_id='".$export_data[$j]['products_id']."'");
				$lang_data->fields['products_description'] = str_replace("\n", "", $lang_data->fields['products_description']);
				$lang_data->fields['products_short_description'] = str_replace("\n", "", $lang_data->fields['products_short_description']);
				$lang_data->fields['products_description'] = str_replace("\r", "", $lang_data->fields['products_description']);
				$lang_data->fields['products_short_description'] = str_replace("\r", "", $lang_data->fields['products_short_description']);
				$lang_data->fields['products_description'] = str_replace(chr(13), "", $lang_data->fields['products_description']);
				$lang_data->fields['products_short_description'] = str_replace(chr(13), "", $lang_data->fields['products_short_description']);
				$line .= $this->TextSign.$lang_data->fields['products_name'].$this->TextSign.$this->seperator;
				$line .= $this->TextSign.$lang_data->fields['products_description'].$this->TextSign.$this->seperator;
				$line .= $this->TextSign.$lang_data->fields['products_short_description'].$this->TextSign.$this->seperator;
				$line .= $this->TextSign.$lang_data->fields['products_meta_title'].$this->TextSign.$this->seperator;
				$line .= $this->TextSign.$lang_data->fields['products_meta_description'].$this->TextSign.$this->seperator;
				$line .= $this->TextSign.$lang_data->fields['products_meta_keywords'].$this->TextSign.$this->seperator;

				$line .= $this->TextSign.$lang_data->fields['products_url'].$this->TextSign.$this->seperator;
			}
			$cat_data = $this->db->db_query("SELECT categories_id FROM ".TABLE_PRODUCTS_TO_CATEGORIES." WHERE products_id='".$export_data[$j]['products_id']."'");

			$line .= $this->buildCAT($cat_data->fields['categories_id']);
			$line .= $this->TextSign;
			$line .= "\n";
			fputs($fp, $line);
		}

		fclose($fp);

		return array (0 => $this->counter, 1 => '', 2 => $this->calcElapsedTime($this->time_start));
	}

	/**
	*   Calculate Elapsed time from 2 given Timestamps
	*   @param int $time old timestamp
	*   @return String elapsed time
	*/
	function calcElapsedTime($time) {

		$diff = time() - $time;
		$daysDiff = 0;
		$hrsDiff = 0;
		$minsDiff = 0;
		$secsDiff = 0;

		$sec_in_a_day = 60 * 60 * 24;
		while ($diff >= $sec_in_a_day) {
			$daysDiff ++;
			$diff -= $sec_in_a_day;
		}
		$sec_in_an_hour = 60 * 60;
		while ($diff >= $sec_in_an_hour) {
			$hrsDiff ++;
			$diff -= $sec_in_an_hour;
		}
		$sec_in_a_min = 60;
		while ($diff >= $sec_in_a_min) {
			$minsDiff ++;
			$diff -= $sec_in_a_min;
		}
		$secsDiff = $diff;

		return ('(elapsed time '.$hrsDiff.'h '.$minsDiff.'m '.$secsDiff.'s)');

	}

	function buildCAT($catID) {

		if (isset($this->CAT[$catID]))
			return $this->CAT[$catID];
		else {
			$cat = array ();
			$tmpID = $catID;

			while ($this->getParent($catID) != 0 || $catID != 0) {
				$cat_data = $this->db->db_query("SELECT categories_name FROM ".TABLE_CATEGORIES_DESCRIPTION." WHERE categories_id='".$catID."' and language_id='".$this->languages[0]['id']."'");
				$catID = $this->getParent($catID);
				$cat[] = $cat_data->fields['categories_name'];

			}
			$catFiller = '';
			for ($i = $this->catDepth - count($cat); $i > 0; $i --)
				$catFiller .= $this->TextSign.$this->TextSign.$this->seperator;

			$catFiller .= $this->TextSign;
			$catStr = '';
			for ($i = count($cat); $i > 0; $i --)
				$catStr .= $this->TextSign.$cat[$i -1].$this->TextSign.$this->seperator;

			$this->CAT[$tmpID] = $catStr.$catFiller;
			return $this->CAT[$tmpID];
		}
	}

	/**
	*   Get the tax_class_id to a given %rate
	*   @return array
	*/
	function getTaxRates() {
		$tax = array ();
		$tax_data = $this->db->db_query("SELECT
												tr.tax_class_id,
												tr.tax_rate,
												ztz.geo_zone_id
											FROM
												".TABLE_TAX_RATES." tr,
												".TABLE_ZONES_TO_GEO_ZONES." ztz
											WHERE
												ztz.zone_country_id='".STORE_COUNTRY."'
											AND
												tr.tax_zone_id=ztz.geo_zone_id");

		while (!$tax_data->EOF) {
			$tax[$tax_data->fields['tax_class_id']] = $tax_data->fields['tax_rate'];
			$tax_data->MoveNext();
		}
		return $tax;
	}

	/**
	*   Prefetch Manufactrers
	*   @return array
	*/
	function getbrand() {
		$man = array ();
		$man_data = $this->db->db_query("SELECT
										brand_name,
										brand_id
									FROM
										".TABLE_BRAND);

		while (!$man_data->EOF) {
			$man[$man_data->fields['brand_id']] = $man_data->fields['brand_name'];
			$man_data->MoveNext();
		}
		return $man;
	}

	/**
	*   Return Parent ID for a given categories id
	*   @return int
	*/
	function getParent($catID) {
		if (isset ($this->PARENT[$catID])) {
			return $this->PARENT[$catID];
		} else {
			$parent = $this->db->db_query("SELECT parent_id FROM ".TABLE_CATEGORIES." WHERE categories_id='".$catID."'");

			$this->PARENT[$catID] = $parent->fields['parent_id'];
			return $parent->fields['parent_id'];
		}
	}
}

class ordersExport {
	var $db;

	function __construct($filename) {
		global $db;

		$this->db = $db;
		$this->time_start = time();

		$this->getOrders();
	}

	function getOrders() {

		$export = $this->db->db_query("SELECT
											o.orders_id,
											os.orders_status_name,
											date_purchased,
											o.customers_name,
											p.products_quantity,
											p.products_model,
											p.products_name,
											REPLACE(REPLACE(REPLACE(FORMAT(p.products_price,2),'.','|'),',','.'),'|',','),
											REPLACE(REPLACE(REPLACE(FORMAT(p.final_price,2),'.','|'),',','.'),'|',',')
										FROM
											".TABLE_ORDERS." AS o
											LEFT JOIN
												".TABLE_ORDERS_STATUS." AS os ON o.orders_status = os.orders_status_id AND os.language_id = 2
											LEFT JOIN
												".TABLE_ORDERS_PRODUCTS." AS p ON o.orders_id = p.orders_id
										ORDER BY
											o.orders_id DESC");

		$data = "Bestellung Nr.;Status;Datum;Kunde;Menge;Produkt Nr.;Produktname;Einzelpreis;Gesamtpreis\n";

        while(!$export->EOF) {

        	$line = "";

        	foreach($export->fields AS $value)
        		$line .= $value.";";

        	$line = substr($line, 0, -1);
        	$line .= "\n";

        	$data .= $line;

        	$export->MoveNext();
		}

		header('Content-Type: application/csv');
		header('Content-Disposition: attachment; filename="'.$filename.'"');
		print $this->getOrders();
		#return $data;
	}
}
?>